<%@ page language="java" contentType="text/javascript; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%

	String num = request.getParameter("num");
	String getLat = request.getParameter("userlat");
	String getLng = request.getParameter("userlng");
	int getTheme = Integer.parseInt(request.getParameter("usertheme"));
	String userselect;
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	PreparedStatement userpstmt = null;
	
	try{
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(
			"jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
	
		String select = "select ID from TingAttendee where TingNumber = ?;";
		pstmt = conn.prepareStatement(select);
		pstmt.setString(1, num);
		ResultSet rs = pstmt.executeQuery();
		
		JSONObject jo = new JSONObject();
		JSONArray ja = new JSONArray();
		while (rs.next()) {
			String id = rs.getString("ID");
			
			switch (getTheme) {
			case 1:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 20 WHEN 2 THEN 17 WHEN 3 THEN 8 WHEN 4 THEN 3 WHEN 5 THEN 15 WHEN 6 THEN 3 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			case 2:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 17 WHEN 2 THEN 20 WHEN 3 THEN 15 WHEN 4 THEN 3 WHEN 5 THEN 8 WHEN 6 THEN 15 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			case 3:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 20 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 12 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			case 4:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 3 WHEN 2 THEN 3 WHEN 3 THEN 3 WHEN 4 THEN 20 WHEN 5 THEN 3 WHEN 6 THEN 3 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			case 5:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 15 WHEN 2 THEN 8 WHEN 3 THEN 5 WHEN 4 THEN 3 WHEN 5 THEN 20 WHEN 6 THEN 5 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			case 6:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" CASE UserTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 12 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 20 ELSE 20 END AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;			
			case 7:
				userselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
						" 20 AS theme"+
						" from UserInfo where ID = ?) a ORDER BY totalvalue DESC;";
				userpstmt = conn.prepareStatement(userselect);
				userpstmt.setString(1, getLat);
				userpstmt.setString(2, getLng);
				userpstmt.setString(3, getLat);
				userpstmt.setString(4, getLat);
				userpstmt.setString(5, getLng);
				userpstmt.setString(6, getLat);
				userpstmt.setString(7, id);
				break;
			}
			ResultSet userrs = userpstmt.executeQuery();
			
			if (userrs.next()) {
				String getid = userrs.getString("ID");
				String getname = userrs.getString("Name");
				String getemail = userrs.getString("Email");
				String getage = userrs.getString("Age");
				String getgender = userrs.getString("Gender");
				String getintroduction = userrs.getString("Introduction");
				if (getintroduction != null) {
					getintroduction = getintroduction.replace("\n","<br>");
					getintroduction = getintroduction.replace("\u0020","&nbsp;");
				}
				String getcacao = userrs.getString("Cacao");
				String getlat = userrs.getString("UserLat");
				String getlng = userrs.getString("UserLng");
				String getdistance = userrs.getString("distance");
				String gettime = userrs.getString("time");
				String gettheme = userrs.getString("theme");
				String getusertheme = userrs.getString("UserTheme");
				String dot = "\\.";
				String gettotalvalue = userrs.getString("totalvalue");
				String[] totalArray = gettotalvalue.split(dot);
				String finaltotal = totalArray[0];
				double totalvalue = Double.parseDouble(gettotalvalue);
				
				if (totalvalue >= 0 && totalvalue < 25){
					String wifi = "0";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 25 && totalvalue <50){
					String wifi = "1";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 50 && totalvalue <75){
					String wifi = "2";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 75 && totalvalue <= 100){
					String wifi = "3";
					jo.put("Wifi", wifi);
				}
				
				jo.put("ID", getid);
				jo.put("Name", getname);
				jo.put("Email", getemail);
				jo.put("Age", getage);
				jo.put("Gender", getgender);
				jo.put("Introduction", getintroduction);
				jo.put("Cacao", getcacao);
				jo.put("UserLat", getlat);
				jo.put("UserLng", getlng);
				jo.put("Distance", getdistance);
				jo.put("Time", gettime);
				jo.put("Theme", gettheme);
				jo.put("UserTheme", getusertheme);
				jo.put("Total", finaltotal);
				
			jo.put("success", "true");
			ja.add(jo);
			}
		} 		
	
		out.print(ja);	
	}
	
	finally{
		try{
			pstmt.close();
		}
		catch (Exception ignored){
		}
		try{
			conn.close();
		}
		catch (Exception ignored){
		}
	}
%>);